package com.ndbg.demo.utils;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class PoiSXSSFWorkbookExcel {

    /**
     * 导出到单个sheet页中
     * @param title
     *            标题
     * @param  result
     *            导出数据内容
     * @return wb
     *            SXSSFWorkbook对象
     * @throws IOException
     *
     */
    public static SXSSFWorkbook getSxssfwbExcel(String sheetTitle, String[] title, List<List<String>> result) {
        SXSSFWorkbook wb = new SXSSFWorkbook();
        //int sheetNum = 0;
        //记录额外创建的sheet数量
        Sheet sheet = wb.createSheet(sheetTitle);
        // wb.setSheetName(sheetNum, sheetTitle+sheetNum);
        int rownum = 0;
        Row row = sheet.createRow(rownum);
        // 设置并获取到需要的样式
        XSSFCellStyle xssfCellStyleHeader = getAndSetXSSFCellStyleHeader(wb);

        Cell cell;
        // 创建标题,此时row=0,即第一行
        for (int j = 0; j < title.length; j++) {
            cell = row.createCell(j);
            cell.setCellValue(title[j]);
            cell.setCellStyle(xssfCellStyleHeader);
        }
        // 遍历集合数据，创建excel内容,产生数据行
        if (result != null) {
            int index = 1;
            List<String> m = null;
            for (int i = 0; i < result.size(); i++) {
                row = sheet.createRow(index);
                int cellIndex = 0;
                m = result.get(i);
                for (String str : m) {
                    row.createCell((short) cellIndex).setCellValue(str);
                    cellIndex++;
                }
                index++;
            }
        }
        return wb;
    }

    /**
     * 获取并设置header样式
     */
    private static XSSFCellStyle getAndSetXSSFCellStyleHeader(SXSSFWorkbook sxssfWorkbook) {
        XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
        Font font = sxssfWorkbook.createFont();
        // 字体大小
        font.setFontHeightInPoints((short) 14);
        // 字体粗细
        font.setBoldweight((short) 20);
        font.setFontName("楷体");
        // 将字体应用到样式上面
        xssfCellStyle.setFont(font);
        // 是否自动换行
        xssfCellStyle.setWrapText(false);
        // 水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return xssfCellStyle;
    }


    public static Workbook getWorkbook(MultipartFile excelFile) {
        String fileName = excelFile.getOriginalFilename();
        String xls = "^.+\\.(?i)(xls)$";
        String xlsx = "^.+\\.(?i)(xlsx)$";
        InputStream inputStream = null;
        try {
            inputStream = excelFile.getInputStream();
            if (Boolean.TRUE.equals(fileName.matches(xls))) {
                // xls 文件
                return new HSSFWorkbook(inputStream);
            } else if (Boolean.TRUE.equals(fileName.matches(xlsx))) {
                // xlsx 文件
                return new XSSFWorkbook(inputStream);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
}
